﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Avanzis.QueryBuilder;
using Avanzis.QueryExecutor;

namespace Avanzis.QueryObjects
{
    /// <summary>
    /// Clase básica de la que heredarán todos los objetos persistentes
    /// </summary>
    public class ActiveRecordBase
    {
        // protected internal static ISessionFactoryHolder holder;

        private QueryObjectSchema schema;
        private Session session;
        
        #region Constructor

        public ActiveRecordBase()
        {
        }

        public ActiveRecordBase(Session session)
        {
            this.session = session;
        }

        #endregion

        #region Properties

        public Session Session
        {
            get { return session; }
            set { session = value; }
        }

        protected QueryObjectSchema Schema
        {
            get
            {
                if (schema == null)
                    schema = QueryObjectSchema.GetSchema(this.GetType());
                return schema;
            }
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Validates this instance.
        /// </summary>
        public virtual void Validate()
        {
        }

        /// <summary>
        /// Saves this instance in the database
        /// </summary>
        public virtual void Save()
        {
            ActiveRecordBase.Save(this);
        }

        public virtual bool Load(object id)
        {
            return Load(id, true);
        }

        /// <summary>
        /// Loads the specified object by primar key
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <returns></returns>
        public virtual bool Load(object id, bool throwOnNotFound)
        {
            
            return ActiveRecordBase.LoadByPrimaryKey(this, id, throwOnNotFound);
            
        }

        /// <summary>
        /// Loads the specified filters.
        /// </summary>
        /// <param name="filters">The filters.</param>
        public virtual bool Load(SearchFiltersCollection filters)
        {
            return Load(filters, true);
        }

        /// <summary>
        /// Loads the instance using filters.
        /// </summary>
        /// <param name="filters">The filters.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <returns></returns>
        public virtual bool Load(SearchFiltersCollection filters, bool throwOnNotFound)
        {
            return ActiveRecordBase.LoadByFilters(this, filters, throwOnNotFound);
        }

        /// <summary>
        /// Fills the specified properties values.
        /// </summary>
        /// <param name="propertiesValues">The properties values.</param>
        public void Fill(IDictionary<string, object> propertiesValues)
        {
            ActiveRecordBase.Fill(this, propertiesValues);
        }

        /// <summary>
        /// Fills the specified reader.
        /// </summary>
        /// <param name="reader">The reader.</param>
        public void Fill(DbDataReader reader)
        {
            ActiveRecordBase.Fill(this, reader);
        }

        public void Fill(DataRow dataRow)
        {
            ActiveRecordBase.Fill(this, dataRow);
        }

        /// <summary>
        /// Check if this instance exists in the database
        /// </summary>
        /// <returns></returns>
        public virtual bool Exists()
        {
            return ActiveRecordBase.Exists(this);
        }

        /// <summary>
        /// Deletes this instance.
        /// </summary>
        public virtual void Delete()
        {
            ActiveRecordBase.Delete(this);
        }

        #endregion

        #region Protected & Private       

        #endregion

        /// <summary>
        /// Loads the by primary key.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <param name="id">The id.</param>
        protected internal static void LoadByPrimaryKey(ActiveRecordBase instance, object id)
        {
            LoadByPrimaryKey(instance, id, true);
        }

        /// <summary>
        /// Finds the object by primary key.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <param name="id">The id.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <returns></returns>
        protected internal static bool LoadByPrimaryKey(ActiveRecordBase instance, object id, bool throwOnNotFound)
        {
            if (instance == null) throw new ArgumentNullException("instance");
            if (id == null) throw new ArgumentNullException("id");

            instance.BeforeLoad();

            bool loaded;

            string field = instance.Schema.PrimaryKey.DatabaseField;

            string parameterName = QueryParameter.GetParameterName(field);

            SelectBuilder builder = GetSelectBuilder(instance);

            builder.Filters.Add(field, parameterName);

            using (DbDataReader reader = ExecuteReader(instance.Session, builder.ToString(), Database.CreateParameter(parameterName, id)))
            {
                if (reader.Read())
                {
                    instance.Fill(reader);
                    instance.OnLoad();
                    loaded = true;
                }
                else
                {
                    if (throwOnNotFound)
                        throw new QueryObjectsException("Object " + instance.GetType().FullName + " not found with primary key " + id);
                    else
                        loaded = false;
                }
            }

            return loaded;
        }

        /// <summary>
        /// Loads the object by filter
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <param name="filters">The filters.</param>
        protected internal static void LoadByFilters(ActiveRecordBase instance, SearchFiltersCollection filters)
        {
            LoadByFilters(instance, filters, true);
        }

        /// <summary>
        /// Loads the by filters.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <param name="filters">The filters.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <returns></returns>
        protected internal static bool LoadByFilters(ActiveRecordBase instance, SearchFiltersCollection filters, bool throwOnNotFound)
        {
            return LoadByFilters(instance, filters, null, true);
        }

        /// <summary>
        /// Loads the by filters.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <param name="filters">The filters.</param>
        /// <param name="orders">The orders.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <returns></returns>
        protected internal static bool LoadByFilters(ActiveRecordBase instance, SearchFiltersCollection filters, OrderItemsCollection orders, bool throwOnNotFound)
        {
            if (instance == null) throw new ArgumentNullException("instance");
            if (filters == null || filters.Count == 0) throw new ArgumentNullException("filters");

            instance.BeforeLoad();

            bool loaded = false;

            SelectBuilder builder = GetSelectBuilder(instance, orders, filters);

            builder.Select.Limit = 1;

            using (DbDataReader reader = ExecuteReader(instance.Session, builder.ToString()))
            {
                if (reader.Read())
                {
                    instance.Fill(reader);
                    loaded = true;
                    instance.OnLoad();
                }
                else
                {
                    if (throwOnNotFound)
                        throw new QueryObjectsException("Object " + instance.GetType().FullName + " not found with filters:  " + filters);
                }
            }

            return loaded;
        }

        /// <summary>
        /// Finds the by primary key.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        protected internal static object FindByPrimaryKey(Type type, object id)
        {
            return FindByPrimaryKey(type, id, false);
        }

        /// <summary>
        /// Finds the by primary key.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="id">The id.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <returns></returns>
        protected internal static object FindByPrimaryKey(Type type, object id, bool throwOnNotFound)
        {
            if (id == null) throw new ArgumentNullException("id");
            if (type == null) throw new ArgumentNullException("type");
            if (!type.IsSubclassOf(typeof (ActiveRecordBase))) throw new QueryObjectsException("La clase " + type.FullName + " no hereda de ActiveRecord");

            ActiveRecordBase returnedObject = (ActiveRecordBase) Activator.CreateInstance(type, null);
            if (returnedObject.Load(id, throwOnNotFound))
                return returnedObject;
            else
                return null;
        }

        /// <summary>
        /// Finds the by primary key.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="id">The id.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <param name="session">The session.</param>
        /// <returns></returns>
        protected internal static object FindByPrimaryKey(Type type, object id, bool throwOnNotFound, Session session)
        {
            if (id == null) throw new ArgumentNullException("id");
            if (type == null) throw new ArgumentNullException("type");
            if (!type.IsSubclassOf(typeof (ActiveRecordBase))) throw new QueryObjectsException("La clase " + type.FullName + " no hereda de ActiveRecord");

            ActiveRecordBase returnedObject = (ActiveRecordBase) Activator.CreateInstance(type, null);
            returnedObject.Session = session;
            if (returnedObject.Load(id, throwOnNotFound))
                return returnedObject;
            else
                return null;
        }


        /// <summary>
        /// Finds the one.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="filters">The filters.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <returns></returns>
        protected internal static object FindOne(Type type, SearchFiltersCollection filters, bool throwOnNotFound)
        {
            return FindOne(type, null, filters, throwOnNotFound);
        }

        /// <summary>
        /// Finds the one.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="filters">The filters.</param>
        /// <param name="orders">The order.</param>
        /// <param name="throwOnNotFound">if set to <c>true</c> [throw on not found].</param>
        /// <returns></returns>
        protected internal static object FindOne(Type type, OrderItemsCollection orders, SearchFiltersCollection filters, bool throwOnNotFound)
        {
            if (type == null) throw new ArgumentNullException("type");
            if (!type.IsSubclassOf(typeof (ActiveRecordBase))) throw new QueryObjectsException("La clase " + type.FullName + " no hereda de ActiveRecord");

            ActiveRecordBase returnedObject = (ActiveRecordBase) Activator.CreateInstance(type, null);

            LoadByFilters(returnedObject, filters, orders, throwOnNotFound);

            return returnedObject;
        }

        /// <summary>
        /// Finds all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        public static Array FindAll(Type type)
        {
            return FindAll(type, null, (SearchFiltersCollection) null);
        }

        /// <summary>
        /// Finds all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="filters">The filters.</param>
        /// <returns></returns>
        public static Array FindAll(Type type, SearchFiltersCollection filters)
        {
            return FindAll(type, null, filters);
        }

        /// <summary>
        /// Finds all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="orders">The orders.</param>
        /// <returns></returns>
        public static Array FindAll(Type type, OrderItemsCollection orders)
        {
            return FindAll(type, orders, null);
        }

        /// <summary>
        /// Finds all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="orderExpression">The order expression.</param>
        /// <param name="filterExpression">The filter expression.</param>
        /// <returns></returns>
        public static Array FindAll(Type type, string orderExpression, string filterExpression)
        {
            OrderItemsCollection orders = string.IsNullOrEmpty(orderExpression) ? null : new OrderItemsCollection(orderExpression);

            SearchFiltersCollection filters = string.IsNullOrEmpty(filterExpression) ? null : new SearchFiltersCollection(filterExpression);

            return FindAll(type, orders, filters);
        }

        /// <summary>
        /// Finds all the objects
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="orders">The orders.</param>
        /// <param name="filters">The filters.</param>
        /// <returns></returns>
        public static Array FindAll(Type type, OrderItemsCollection orders, SearchFiltersCollection filters)
        {
            if (type == null) throw new ArgumentNullException("type");

            if (!type.IsSubclassOf(typeof (ActiveRecordBase))) throw new QueryObjectsException("La clase " + type.FullName + " no hereda de ActiveRecord");

            SelectBuilder selectBuilder = GetSelectBuilder(type, orders, filters);

            string sql = selectBuilder.ToString();

            ArrayList objectsFound = ExecuteObjectArray(type, sql);

            return QueryObjectsHelper.BuildArray(type, objectsFound);
        }

        /// <summary>
        /// Executes the commandText and returns an Array of objects of the especified type
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="commandText">The sql command</param>
        /// <returns></returns>
        private static ArrayList ExecuteObjectArray(Type type, string commandText)
        {
            ArrayList objectsFound = new ArrayList();

            using (DbDataReader reader = ExecuteReader(null, commandText))
            {
                while (reader.Read())
                {
                    ActiveRecordBase objectFound = (ActiveRecordBase) Activator.CreateInstance(type);

                    objectFound.Fill(reader);

                    objectsFound.Add(objectFound);
                }
            }

            return objectsFound;
        }

        #region SlicedFindAll

        /// <summary>
        /// Sliceds the find all ordered.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="firstResult">The first results.</param>
        /// <param name="maxResults">The max results.</param>
        /// <param name="orderExpression">The order expression.</param>
        /// <returns></returns>
        public static Array SlicedFindAllOrdered(Type type, int firstResult, int maxResults, string orderExpression)
        {
            OrderItemsCollection orders = (!string.IsNullOrEmpty(orderExpression)) ? new OrderItemsCollection(orderExpression) : null;

            if (firstResult < 0) firstResult = 0;
            if (maxResults <= 0) maxResults = Int32.MaxValue;

            if (firstResult == 0 && maxResults == Int32.MaxValue)
            {
                return FindAll(type, orders);
            }
            else
            {
                return SlicedFindAll(type, firstResult, maxResults, orders, null);
            }
        }

        /// <summary>
        /// Sliceds the find all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="firstResult">The first results.</param>
        /// <param name="maxResults">The max results.</param>
        /// <returns></returns>
        public static Array SlicedFindAll(Type type, int firstResult, int maxResults)
        {
            return SlicedFindAll(type, firstResult, maxResults, null, null);
        }

        /// <summary>
        /// Sliceds the find all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="firstResult">The first results.</param>
        /// <param name="maxResults">The max results.</param>
        /// <param name="orders">The orders.</param>
        /// <returns></returns>
        public static Array SlicedFindAll(Type type, int firstResult, int maxResults, OrderItemsCollection orders)
        {
            return SlicedFindAll(type, firstResult, maxResults, orders, null);
        }

        /// <summary>
        /// Sliceds the find all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="firstResult">The first results (index base 0).</param>
        /// <param name="maxResults">The max results.</param>
        /// <param name="filters">The filters.</param>
        /// <returns></returns>
        public static Array SlicedFindAll(Type type, int firstResult, int maxResults, SearchFiltersCollection filters)
        {
            return SlicedFindAll(type, firstResult, maxResults, null, filters);
        }

        /// <summary>
        /// Sliceds the find all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="firstResult">The first result.</param>
        /// <param name="maxResults">The max results.</param>
        /// <param name="orders">The orders.</param>
        /// <param name="filters">The filters.</param>
        /// <returns></returns>
        public static Array SlicedFindAll(Type type, int firstResult, int maxResults, OrderItemsCollection orders, SearchFiltersCollection filters)
        {
            if (type == null) throw new ArgumentNullException("type");

            if (!type.IsSubclassOf(typeof (ActiveRecordBase))) throw new QueryObjectsException("La clase " + type.FullName + " no hereda de ActiveRecord");

            SelectBuilder selectBuilder = GetSelectBuilder(type, orders, filters);

            int lastResult = firstResult + maxResults;

            // Si se suma a Int32.MaxValue un número, da la vuelta y comienza con los negativos...
            if (lastResult < 0) lastResult = Int32.MaxValue;

            ArrayList objectsFound;

            if (QueryObjectsConfiguration.DialectAllowsOffset)
            {
                int offset = firstResult - 1;
                int limit = maxResults;

                selectBuilder.Select.Offset = offset;
                selectBuilder.Select.Limit = limit;

                string sql = selectBuilder.ToString();

                objectsFound = ExecuteObjectArray(type, sql);
            }
            else
            {
                int firstIndex = firstResult - 1;

                int selectLimit = firstIndex + maxResults;

                int lastIndex = selectLimit - 1;

                selectBuilder.Select.Limit = selectLimit;

                ArrayList allObjects = ExecuteObjectArray(type, selectBuilder.ToString());

                if (lastResult > allObjects.Count)
                    lastResult = allObjects.Count;

                objectsFound = new ArrayList();

                for (int i = firstIndex; i <= lastIndex; i++)
                {
                    objectsFound.Add(allObjects[i]);
                }
            }

            return QueryObjectsHelper.BuildArray(type, objectsFound);
        }

        #endregion

        #region CountAll

        /// <summary>
        /// Counts all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        public static int CountAll(Type type)
        {
            return CountAll(type, (SearchFiltersCollection) null);
        }

        /// <summary>
        /// Counts all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="filterExpression">The filter expression.</param>
        /// <returns></returns>
        public static int CountAll(Type type, string filterExpression)
        {
            SearchFiltersCollection filters = string.IsNullOrEmpty(filterExpression) ? null : new SearchFiltersCollection(filterExpression);

            return CountAll(type, filters);
        }

        /// <summary>
        /// Counts all.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="filters">The filters.</param>
        /// <returns></returns>
        public static int CountAll(Type type, SearchFiltersCollection filters)
        {
            if (type == null) throw new ArgumentNullException("type");

            if (!type.IsSubclassOf(typeof (ActiveRecordBase))) throw new QueryObjectsException("La clase " + type.FullName + " no hereda de ActiveRecord");

            SelectBuilder selectBuilder = GetSelectBuilder(type);

            selectBuilder.Fields.Clear();
            selectBuilder.Fields.Add("COUNT(*)");

            if (filters != null && filters.Count > 0)
            {
                for (int i = 0; i < filters.Count; i++)
                {
                    selectBuilder.Where.Filters.Add(filters[i]);
                }
            }

            //ArrayList objectsFound = new ArrayList();

            int objectCount = Convert.ToInt32(ExecuteScalar(null, selectBuilder.ToString()));

            return objectCount;
        }

        #endregion

        #region Delete

        /// <summary>
        /// Deletes the specified obj.
        /// </summary>
        /// <param name="obj">The obj.</param>
        protected internal static void Delete(object obj)
        {
            ActiveRecordBase instance = obj as ActiveRecordBase;
            if (instance == null) throw new ArgumentNullException();
            if (instance.Schema.PrimaryKey == null) throw new QueryObjectsException("This class does not have a primary key");

            instance.BeforeDelete();

            string field = instance.Schema.PrimaryKey.DatabaseField;
            object value = instance.Schema.PrimaryKey.GetValue(instance);

            if (value == null) throw new QueryObjectsException("La clave primaria es nula");

            DeleteBuilder deleteBuilder = new DeleteBuilder(instance.Schema.TableName);
            deleteBuilder.Where.Filters.Add(field, SqlOperator.Equals, value, ExpressionType.Primitive);

            ExecuteNonQuery(instance.Session, deleteBuilder.ToString());
        }

        /// <summary>
        /// Deletes the specified instance.
        /// </summary>
        /// <param name="instance">The instance.</param>
        public static void Delete(ActiveRecordBase instance)
        {
            if (instance == null) throw new ArgumentNullException("instance");

            string field = instance.Schema.PrimaryKey.DatabaseField;
            string parameterName = QueryParameter.GetParameterName(field);
            object value = instance.Schema.PrimaryKey.GetValue(instance);

            DeleteBuilder builder = GetDeleteBuilder(instance);

            builder.Filters.Add(field, parameterName);

            ExecuteNonQuery(instance.Session, builder.ToString(), Database.CreateParameter(parameterName, value));
            
        }

        #endregion

        /// <summary>
        /// Fills the specified object.
        /// </summary>
        /// <param name="obj">The object.</param>
        /// <param name="propertiesValues">The properties values.</param>
        public static void Fill(object obj, IDictionary<string, object> propertiesValues)
        {
            ActiveRecordBase instance = obj as ActiveRecordBase;
            if (instance == null) throw new ArgumentNullException();

            // Rellena la key principal
            instance.Schema.PrimaryKey.SetValue(instance, propertiesValues[instance.Schema.PrimaryKey.DatabaseField]);
            // Rellena el resto de propiedades propiedades
            foreach (PersistentPropertyInfo property in instance.Schema.PersistentProperties.Values)
            {
                try
                {
                    property.SetValue(instance, propertiesValues[property.DatabaseField]);
                }
                catch (Exception ex)
                {
                    throw new QueryObjectsException("The database field " + property.DatabaseField + " is not mapped to a property", ex);
                }
            }

            instance.OnFill();
        }

        /// <summary>
        /// Fills the specified object
        /// </summary>
        /// <param name="obj">The obj.</param>
        /// <param name="reader">The reader.</param>
        public static void Fill(object obj, DbDataReader reader)
        {
            ActiveRecordBase instance = obj as ActiveRecordBase;
            if (instance == null) throw new ArgumentNullException();

            // Rellena la key principal
            instance.Schema.PrimaryKey.SetValue(instance, reader[instance.Schema.PrimaryKey.DatabaseField]);

            // Rellena el resto de propiedades propiedades
            foreach (PersistentPropertyInfo property in instance.Schema.PersistentProperties.Values)
            {
                try
                {
                    property.SetValue(instance, Database.Provider.ProcessDatabaseValue(reader[property.DatabaseField]));
                }
                catch (Exception ex)
                {
                    throw new QueryObjectsException("The database field " + property.DatabaseField + " is not correctly mapped to a property", ex);
                }
            }

            instance.OnFill();
        }

        /// <summary>
        /// Fills the specified obj.
        /// </summary>
        /// <param name="obj">The obj.</param>
        /// <param name="dataRow">The data row.</param>
        public static void Fill(object obj, DataRow dataRow)
        {
            ActiveRecordBase instance = obj as ActiveRecordBase;
            if (instance == null) throw new ArgumentNullException();

            // Rellena la key principal
            instance.Schema.PrimaryKey.SetValue(instance, dataRow[instance.Schema.PrimaryKey.DatabaseField]);

            // Rellena el resto de propiedades propiedades
            foreach (PersistentPropertyInfo property in instance.Schema.PersistentProperties.Values)
            {
                try
                {
                    property.SetValue(instance, dataRow[property.DatabaseField]);
                }
                catch (Exception ex)
                {
                    throw new QueryObjectsException("The database field " + property.DatabaseField + " is not correctly mapped to a property", ex);
                }
            }

            instance.OnFill();
        }

        /// <summary>
        /// Saves the specified object.
        /// </summary>
        /// <param name="obj">The obj.</param>
        protected internal static void Save(object obj)
        {
            ActiveRecordBase instance = obj as ActiveRecordBase;
            if (instance == null) throw new ArgumentNullException();

            instance.BeforeSave();

            instance.Validate();

            SaveBuilder saveBuilder = GetSaveBuilder(instance);

            bool isInsert = (saveBuilder is InsertBuilder);

            ParameterCollection parameters = new ParameterCollection();

            foreach (PersistentPropertyInfo property in instance.Schema.PersistentProperties.Values)
            {
                if (property.PropertyAttribute.Insert && isInsert || property.PropertyAttribute.Update && !isInsert)
                {
                    // Obtiene el nombre de la propiedad
                    string field = property.DatabaseField;
                    string parameterName = QueryParameter.GetParameterName(field);

                    if (saveBuilder.FieldValues.ContainsKey(field))
                        throw new QueryObjectsException("La propiedad " + property.PropertyInfo.Name + " del objeto " + instance.Schema.ObjectType.FullName + " indica el mismo campo de la base de datos que otra propiedad del objeto. Sólo puede existir una propiedad por campo de la base de datos");

                    saveBuilder.FieldValues.Add(field, parameterName);

                    object propertyValue = property.GetValue(instance);
                    if (propertyValue == null && property.PropertyAttribute.NotNull)
                        throw new QueryObjectsException("Property " + property.PropertyInfo.Name + " can not be NULL");

                    parameters.Add(parameterName, propertyValue);
                }
            }

            // En función del generador del PrimaryKey debe hacer una cosa u otra
            switch (instance.Schema.PrimaryKey.Generator)
            {
                case PrimaryKeyType.Identity:
                    string sql = saveBuilder.ToString();
                    if (saveBuilder is InsertBuilder) // Si está insertando debe guardar en la propiedad la identidad
                    {
                        sql = string.Concat(sql, ";", "SELECT @@Identity");

                        object identityValue = ExecuteScalar(instance.Session, sql, parameters);

                        instance.Schema.PrimaryKey.SetValue(instance, identityValue);
                    }
                    else // Si está actualizando, no tiene que hacer nada
                    {
                        int affectedRows = ExecuteNonQuery(instance.Session, sql, parameters);
                        if (affectedRows == 0)
                            throw new QueryObjectsException("No rows has been affected by an update, have you assigned a correct primary key?");
                    }
                    break;
                case PrimaryKeyType.Assigned:
                    string primaryKeyField = instance.Schema.PrimaryKey.DatabaseField;
                    string parameterName = QueryParameter.GetParameterName(primaryKeyField);
                    // Lo añade al SaveBuilder
                    saveBuilder.FieldValues.Add(primaryKeyField, parameterName);
                    // Lo añade a los parámetros
                    parameters.Add(parameterName, instance.Schema.PrimaryKey.GetValue(instance));

                    sql = saveBuilder.ToString();

                    ExecuteNonQuery(instance.Session, sql, parameters);

                    break;
            }

            instance.OnSave();
        }


        /// <summary>
        /// Gets the select builder.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        public static SelectBuilder GetSelectBuilder(Type type)
        {
            return GetSelectBuilder(type, null, null);
        }

        /// <summary>
        /// Gets the select builder.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <param name="orders">The orders.</param>
        /// <param name="filters">The filters.</param>
        /// <returns></returns>
        public static SelectBuilder GetSelectBuilder(Type type, OrderItemsCollection orders, SearchFiltersCollection filters)
        {
            if (type == null) throw new ArgumentNullException("type");

            QueryObjectSchema schema = QueryObjectSchema.GetSchema(type);

            SelectBuilder selectBuilder = new SelectBuilder();
            selectBuilder.TableName = schema.TableName;
            selectBuilder.Fields.Add("*");

            if (filters != null && filters.Count > 0)
            {
                for (int i = 0; i < filters.Count; i++)
                {
                    selectBuilder.Where.Filters.Add(filters[i]);
                }
            }

            if (orders != null && orders.Count > 0)
            {
                for (int i = 0; i < orders.Count; i++)
                {
                    selectBuilder.OrderBy.Fields.Add(orders[i]);
                }
            }

            return selectBuilder;
        }

        /// <summary>
        /// Gets the select builder.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <param name="orders">The orders.</param>
        /// <param name="filters">The filters.</param>
        /// <returns></returns>
        public static SelectBuilder GetSelectBuilder(ActiveRecordBase instance, OrderItemsCollection orders, SearchFiltersCollection filters)
        {
            if (instance == null) throw new ArgumentNullException("instance");
            if (instance.Schema.PrimaryKey == null) throw new QueryObjectsException("There is no primary key in this object");

            return GetSelectBuilder(instance.GetType(), orders, filters);
        }

        /// <summary>
        /// Gets the select builder.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <returns></returns>
        public static SelectBuilder GetSelectBuilder(ActiveRecordBase instance)
        {
            return GetSelectBuilder(instance, null, null);
        }

        /// <summary>
        /// Gets the save builder.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <returns></returns>
        public static SaveBuilder GetSaveBuilder(ActiveRecordBase instance)
        {
            if (instance == null) throw new ArgumentNullException("instance");
            if (instance.Schema.PrimaryKey == null) throw new QueryObjectsException("There is no primary key in this object");
            object primaryKeyValue = instance.Schema.PrimaryKey.GetValue(instance);
            SaveBuilder saveBuilder;
            bool update = false;
            // Si tiene un id es que tiene que actualizar
            switch (instance.Schema.PrimaryKey.Generator)
            {
                case PrimaryKeyType.Identity:
                    if (primaryKeyValue != null)
                    {
                        // Si es un entero
                        Decimal id;
                        if (Decimal.TryParse(primaryKeyValue.ToString(), out id))
                        {
                            if (id > 0)
                                update = true;
                            else
                                update = false;
                        }
                        else
                        {
                            throw new QueryObjectsException("Field " + instance.Schema.PrimaryKey.PropertyInfo.Name + " must be an interger because it is PrimaryKeyType.Identity");
                        }
                    }
                    break;
                case PrimaryKeyType.Assigned:
                    if (Exists(instance))
                        update = true;
                    break;
            }

            if (update)
            {
                string primaryKeyField = instance.Schema.PrimaryKey.DatabaseField;
                saveBuilder = new UpdateBuilder();
                ((UpdateBuilder) saveBuilder).Where.Filters.Add(primaryKeyField, SqlOperator.Equals, primaryKeyValue, ExpressionType.Primitive);
            }
            else
            {
                saveBuilder = new InsertBuilder();
            }
            saveBuilder.TableName = instance.Schema.TableName;
            return saveBuilder;
        }

        /// <summary>
        /// Gets the select builder.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <returns></returns>
        public static DeleteBuilder GetDeleteBuilder(ActiveRecordBase instance)
        {
            if (instance == null) throw new ArgumentNullException("instance");
            if (instance.Schema.PrimaryKey == null) throw new QueryObjectsException("There is no primary key in this object");

            DeleteBuilder deleteBuilder = new DeleteBuilder(instance.Schema.TableName);

            return deleteBuilder;
        }

        /// <summary>
        /// Exists the specified instance in the database.
        /// </summary>
        /// <param name="instance">The instance.</param>
        /// <returns></returns>
        protected static bool Exists(ActiveRecordBase instance)
        {
            if (instance == null) throw new ArgumentNullException("instance");
            if (instance.Schema.PrimaryKey == null) throw new QueryObjectsException("There is no primary key in this object");

            string primaryKeyField = instance.Schema.PrimaryKey.DatabaseField;
            object primaryKeyValue = instance.Schema.PrimaryKey.GetValue(instance);

            if (primaryKeyField == null)
                return false;

            SelectBuilder selectBuilder = new SelectBuilder();
            selectBuilder.From.TableName = instance.Schema.TableName;
            selectBuilder.Fields.Add("Count(*)");
            selectBuilder.Where.Filters.Add(primaryKeyField, SqlOperator.Equals, primaryKeyValue, ExpressionType.Primitive);

            string sqlSelect = selectBuilder.ToString();

            int count = Convert.ToInt32(ExecuteScalar(instance.Session, sqlSelect));
            return count > 0;
        }

        #region Data Access

        protected static object ExecuteScalar(Session session, string commandText)
        {
            return ExecuteScalar(session, commandText, null);
        }

        protected static object ExecuteScalar(Session session, string commandText, params DbParameter[] parameters)
        {
            if (session == null || !session.IsOpen)
            {
                return Database.ExecuteScalar(QueryObjectsConfiguration.ConnectionString, CommandType.Text, commandText, parameters);
            }
            else if (session.IsInActiveTransaction)
            {
                return Database.ExecuteScalar(session.Transaction, CommandType.Text, commandText, parameters);
            }
            else
            {
                return Database.ExecuteScalar(session.Connection, CommandType.Text, commandText, parameters);
            }
        }

        protected static int ExecuteNonQuery(Session session, string commandText)
        {
            return ExecuteNonQuery(session, commandText, null);
        }

        protected static int ExecuteNonQuery(Session session, string commandText, params DbParameter[] parameters)
        {
            if (session == null || !session.IsOpen)
            {
                return Database.ExecuteNonQuery(QueryObjectsConfiguration.ConnectionString, CommandType.Text, commandText, parameters);
            }
            else if (session.IsInActiveTransaction)
            {
                return Database.ExecuteNonQuery(session.Transaction, CommandType.Text, commandText, parameters);
            }
            else
            {
                return Database.ExecuteNonQuery(session.Connection, CommandType.Text, commandText, parameters);
            }
        }

        protected static DbDataReader ExecuteReader(Session session, string commandText)
        {
            return ExecuteReader(session, commandText, null);
        }

        protected static DbDataReader ExecuteReader(Session session, string commandText, params DbParameter[] parameters)
        {
            if (session == null || !session.IsOpen)
            {
                return Database.ExecuteReader(QueryObjectsConfiguration.ConnectionString, CommandType.Text, commandText, parameters);
            }
            else if (session.IsInActiveTransaction)
            {
                return Database.ExecuteReader(session.Transaction, CommandType.Text, commandText, parameters);
            }
            else
            {
                return Database.ExecuteReader(session.Connection, CommandType.Text, commandText, parameters);
            }
        }

        #endregion

        /// <summary>
        /// Método que se llama antes de cargar el objeto
        /// </summary>
        protected virtual void BeforeLoad()
        {
        }
        /// <summary>
        /// Método que se llaman en cuanto se carga el objeto
        /// </summary>
        protected virtual void OnLoad()
        {
        }

        /// <summary>
        /// Método que se llama en cuanto se rellena el objeto
        /// </summary>
        protected virtual void OnFill()
        {
        }

        /// <summary>
        /// Method called after object is saved
        /// </summary>
        protected virtual void OnSave()
        {
        }

        /// <summary>
        /// Method called before object is saved
        /// </summary>
        protected virtual void BeforeSave()
        {
        }

        /// <summary>
        /// Method called before object is deleted
        /// </summary>
        protected virtual void BeforeDelete()
        {
        }
    }
}